﻿ 
 

 
 
/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MembersInModules_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MembersInModules_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MembersInModules_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @MemberId Int   =  NULL 
, @ModuleId Int   =  NULL 
	AS
			WITH CTE_MembersInModules AS
			( 
			SELECT
 							MemberId,
								ModuleId,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''MemberId'' THEN MemberId
												WHEN @OrderColume=''ModuleId'' THEN ModuleId
									ELSE 
							    MemberId
					        	END
			) AS "RowNumber"
  			FROM MembersInModules 
			where 
								(MemberId = @MemberId OR @MemberId Is Null)
										AND(ModuleId = @ModuleId OR @ModuleId Is Null)
								)
			SELECT  *
			FROM CTE_MembersInModules
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MembersInModules_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MembersInModules_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MembersInModules_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MembersInModules_Delete
		@MemberId Int 		  =  NULL 
				, @ModuleId Int 					  =  NULL 
								AS
			Delete From MembersInModules
			Where 	
								(MemberId=@MemberId)
										AND(ModuleId=@ModuleId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MembersInModules_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MembersInModules_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MembersInModules_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MembersInModules_Insert
	@MemberId Int   =  NULL 
		, @ModuleId Int   =  NULL 
		AS
			INSERT INTO MembersInModules
			(
									MemberId
												,ModuleId
									)
			Values(
									@MemberId
												,@ModuleId
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MembersInModules_Insert done'
/*===========================Start Update PROCEDURE=========================*/
	/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberPhones_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberPhones_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberPhones_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @MemberId Int   =  NULL 
, @Phone VarChar (50)  =  NULL 
, @PhoneId Int   =  NULL 
, @PhoneType TinyInt   =  NULL 
	AS
			WITH CTE_MemberPhones AS
			( 
			SELECT
 							MemberId,
								Phone,
								PhoneId,
								PhoneType,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''MemberId'' THEN MemberId
						 
						WHEN @OrderColume=''Phone'' THEN Phone
												WHEN @OrderColume=''PhoneId'' THEN PhoneId
												WHEN @OrderColume=''PhoneType'' THEN PhoneType
									ELSE 
							    PhoneId
					        	END
			) AS "RowNumber"
  			FROM MemberPhones 
			where 
								(MemberId = @MemberId OR @MemberId Is Null)
										AND(Phone LIKE @Phone OR @Phone Is Null)
										AND(PhoneId = @PhoneId OR @PhoneId Is Null)
										AND(PhoneType = @PhoneType OR @PhoneType Is Null)
								)
			SELECT  *
			FROM CTE_MemberPhones
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberPhones_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberPhones_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberPhones_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberPhones_Delete
		@PhoneId Int 		  =  NULL 
					AS
			Delete From MemberPhones
			Where 	
								(PhoneId=@PhoneId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MemberPhones_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberPhones_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberPhones_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberPhones_Insert
	@MemberId Int   =  NULL 
		, @Phone VarChar (50)  =  NULL 
		, @PhoneType TinyInt   =  NULL 
		AS
			INSERT INTO MemberPhones
			(
									MemberId
												,Phone
												,PhoneType
									)
			Values(
									@MemberId
												,@Phone
												,@PhoneType
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MemberPhones_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberPhones_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberPhones_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_MemberPhones_UPDATE
												@MemberId Int 							  =  NULL 
														, @Phone VarChar 							(50)  =  NULL 
														, @PhoneId Int 							  =  NULL 
														, @PhoneType TinyInt 							  =  NULL 
												AS
					UPDATE MemberPhones SET
													MemberId=@MemberId 
																,Phone=@Phone
																,PhoneType=@PhoneType
													Where 	
												PhoneId=@PhoneId
							'					
					PRINT 'dbo.SP_MemberPhones_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Members_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Members_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Members_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Birthdate DateTime   =  NULL 
, @ConfessionFather NChar (10)  =  NULL 
, @CountryId Int   =  NULL 
, @CreationDate DateTime   =  NULL 
, @DisplayName NVarChar (256)  =  NULL 
, @Email NVarChar (256)  =  NULL 
, @FirstName NVarChar (256)  =  NULL 
, @Gender TinyInt   =  NULL 
, @ImagePath NVarChar (50)  =  NULL 
, @IsActive Bit   =  NULL 
, @LastName NVarChar (256)  =  NULL 
, @LastUpdateDate DateTime   =  NULL 
, @MemberId Int   =  NULL 
, @NationalityId Int   =  NULL 
, @Password NVarChar (500)  =  NULL 
	AS
			WITH CTE_Members AS
			( 
			SELECT
 							Birthdate,
								ConfessionFather,
								CountryId,
								CreationDate,
								DisplayName,
								Email,
								FirstName,
								Gender,
								ImagePath,
								IsActive,
								LastName,
								LastUpdateDate,
								MemberId,
								NationalityId,
								Password,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''Birthdate'' THEN Birthdate
						 
						WHEN @OrderColume=''ConfessionFather'' THEN ConfessionFather
												WHEN @OrderColume=''CountryId'' THEN CountryId
												WHEN @OrderColume=''CreationDate'' THEN CreationDate
						 
						WHEN @OrderColume=''DisplayName'' THEN DisplayName
						 
						WHEN @OrderColume=''Email'' THEN Email
						 
						WHEN @OrderColume=''FirstName'' THEN FirstName
												WHEN @OrderColume=''Gender'' THEN Gender
						 
						WHEN @OrderColume=''ImagePath'' THEN ImagePath
												WHEN @OrderColume=''IsActive'' THEN IsActive
						 
						WHEN @OrderColume=''LastName'' THEN LastName
												WHEN @OrderColume=''LastUpdateDate'' THEN LastUpdateDate
												WHEN @OrderColume=''MemberId'' THEN MemberId
												WHEN @OrderColume=''NationalityId'' THEN NationalityId
						 
						WHEN @OrderColume=''Password'' THEN Password
									ELSE 
							    MemberId
					        	END
			) AS "RowNumber"
  			FROM Members 
			where 
								(Birthdate = @Birthdate OR @Birthdate Is Null)
										AND(ConfessionFather LIKE @ConfessionFather OR @ConfessionFather Is Null)
										AND(CountryId = @CountryId OR @CountryId Is Null)
										AND(CreationDate = @CreationDate OR @CreationDate Is Null)
										AND(DisplayName LIKE @DisplayName OR @DisplayName Is Null)
										AND(Email LIKE @Email OR @Email Is Null)
										AND(FirstName LIKE @FirstName OR @FirstName Is Null)
										AND(Gender = @Gender OR @Gender Is Null)
										AND(ImagePath LIKE @ImagePath OR @ImagePath Is Null)
										AND(IsActive = @IsActive OR @IsActive Is Null)
										AND(LastName LIKE @LastName OR @LastName Is Null)
										AND(LastUpdateDate = @LastUpdateDate OR @LastUpdateDate Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(NationalityId = @NationalityId OR @NationalityId Is Null)
										AND(Password LIKE @Password OR @Password Is Null)
								)
			SELECT  *
			FROM CTE_Members
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Members_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Members_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Members_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Members_Delete
		@MemberId Int 		  =  NULL 
					AS
			Delete From Members
			Where 	
								(MemberId=@MemberId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Members_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Members_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Members_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Members_Insert
	@Birthdate DateTime   =  NULL 
		, @ConfessionFather NChar (10)  =  NULL 
		, @CountryId Int   =  NULL 
		, @CreationDate DateTime   =  NULL 
		, @DisplayName NVarChar (256)  =  NULL 
		, @Email NVarChar (256)  =  NULL 
		, @FirstName NVarChar (256)  =  NULL 
		, @Gender TinyInt   =  NULL 
		, @ImagePath NVarChar (50)  =  NULL 
		, @IsActive Bit   =  NULL 
		, @LastName NVarChar (256)  =  NULL 
		, @LastUpdateDate DateTime   =  NULL 
		, @NationalityId Int   =  NULL 
		, @Password NVarChar (500)  =  NULL 
		AS
			INSERT INTO Members
			(
									Birthdate
												,ConfessionFather
												,CountryId
												,CreationDate
												,DisplayName
												,Email
												,FirstName
												,Gender
												,ImagePath
												,IsActive
												,LastName
												,LastUpdateDate
												,NationalityId
												,Password
									)
			Values(
									@Birthdate
												,@ConfessionFather
												,@CountryId
												,@CreationDate
												,@DisplayName
												,@Email
												,@FirstName
												,@Gender
												,@ImagePath
												,@IsActive
												,@LastName
												,@LastUpdateDate
												,@NationalityId
												,@Password
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Members_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Members_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Members_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Members_UPDATE
												@Birthdate DateTime 							  =  NULL 
														, @ConfessionFather NChar 							(10)  =  NULL 
														, @CountryId Int 							  =  NULL 
														, @CreationDate DateTime 							  =  NULL 
														, @DisplayName NVarChar 							(256)  =  NULL 
														, @Email NVarChar 							(256)  =  NULL 
														, @FirstName NVarChar 							(256)  =  NULL 
														, @Gender TinyInt 							  =  NULL 
														, @ImagePath NVarChar 							(50)  =  NULL 
														, @IsActive Bit 							  =  NULL 
														, @LastName NVarChar 							(256)  =  NULL 
														, @LastUpdateDate DateTime 							  =  NULL 
														, @MemberId Int 							  =  NULL 
														, @NationalityId Int 							  =  NULL 
														, @Password NVarChar 							(500)  =  NULL 
												AS
					UPDATE Members SET
													Birthdate=@Birthdate 
																,ConfessionFather=@ConfessionFather
																,CountryId=@CountryId
																,CreationDate=@CreationDate
																,DisplayName=@DisplayName
																,Email=@Email
																,FirstName=@FirstName
																,Gender=@Gender
																,ImagePath=@ImagePath
																,IsActive=@IsActive
																,LastName=@LastName
																,LastUpdateDate=@LastUpdateDate
																,NationalityId=@NationalityId
																,Password=@Password
													Where 	
												MemberId=@MemberId
							'					
					PRINT 'dbo.SP_Members_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberAddresses_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberAddresses_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberAddresses_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @AddressId Int   =  NULL 
, @AppartmentNumber NChar (10)  =  NULL 
, @BuildingNumber NChar (10)  =  NULL 
, @Directions NVarChar (Max)  =  NULL 
, @FloorNumber NChar (10)  =  NULL 
, @MemberId Int   =  NULL 
, @Street NVarChar (256)  =  NULL 
, @ZipCode NChar (10)  =  NULL 
	AS
			WITH CTE_MemberAddresses AS
			( 
			SELECT
 							AddressId,
								AppartmentNumber,
								BuildingNumber,
								Directions,
								FloorNumber,
								MemberId,
								Street,
								ZipCode,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''AddressId'' THEN AddressId
						 
						WHEN @OrderColume=''AppartmentNumber'' THEN AppartmentNumber
						 
						WHEN @OrderColume=''BuildingNumber'' THEN BuildingNumber
						 
						WHEN @OrderColume=''Directions'' THEN Directions
						 
						WHEN @OrderColume=''FloorNumber'' THEN FloorNumber
												WHEN @OrderColume=''MemberId'' THEN MemberId
						 
						WHEN @OrderColume=''Street'' THEN Street
						 
						WHEN @OrderColume=''ZipCode'' THEN ZipCode
									ELSE 
							    AddressId
					        	END
			) AS "RowNumber"
  			FROM MemberAddresses 
			where 
								(AddressId = @AddressId OR @AddressId Is Null)
										AND(AppartmentNumber LIKE @AppartmentNumber OR @AppartmentNumber Is Null)
										AND(BuildingNumber LIKE @BuildingNumber OR @BuildingNumber Is Null)
										AND(Directions LIKE @Directions OR @Directions Is Null)
										AND(FloorNumber LIKE @FloorNumber OR @FloorNumber Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Street LIKE @Street OR @Street Is Null)
										AND(ZipCode LIKE @ZipCode OR @ZipCode Is Null)
								)
			SELECT  *
			FROM CTE_MemberAddresses
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberAddresses_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberAddresses_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberAddresses_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberAddresses_Delete
		@AddressId Int 		  =  NULL 
					AS
			Delete From MemberAddresses
			Where 	
								(AddressId=@AddressId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MemberAddresses_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberAddresses_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberAddresses_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberAddresses_Insert
	@AppartmentNumber NChar (10)  =  NULL 
		, @BuildingNumber NChar (10)  =  NULL 
		, @Directions NVarChar (Max)  =  NULL 
		, @FloorNumber NChar (10)  =  NULL 
		, @MemberId Int   =  NULL 
		, @Street NVarChar (256)  =  NULL 
		, @ZipCode NChar (10)  =  NULL 
		AS
			INSERT INTO MemberAddresses
			(
									AppartmentNumber
												,BuildingNumber
												,Directions
												,FloorNumber
												,MemberId
												,Street
												,ZipCode
									)
			Values(
									@AppartmentNumber
												,@BuildingNumber
												,@Directions
												,@FloorNumber
												,@MemberId
												,@Street
												,@ZipCode
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MemberAddresses_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberAddresses_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberAddresses_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_MemberAddresses_UPDATE
												@AddressId Int 							  =  NULL 
														, @AppartmentNumber NChar 							(10)  =  NULL 
														, @BuildingNumber NChar 							(10)  =  NULL 
														, @Directions NVarChar (Max)							  =  NULL 
														, @FloorNumber NChar 							(10)  =  NULL 
														, @MemberId Int 							  =  NULL 
														, @Street NVarChar 							(256)  =  NULL 
														, @ZipCode NChar 							(10)  =  NULL 
												AS
					UPDATE MemberAddresses SET
													AppartmentNumber=@AppartmentNumber 
																,BuildingNumber=@BuildingNumber
																,Directions=@Directions
																,FloorNumber=@FloorNumber
																,MemberId=@MemberId
																,Street=@Street
																,ZipCode=@ZipCode
													Where 	
												AddressId=@AddressId
							'					
					PRINT 'dbo.SP_MemberAddresses_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Countries_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Countries_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Countries_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @CountryId Int   =  NULL 
, @CountryName NVarChar (256)  =  NULL 
	AS
			WITH CTE_Countries AS
			( 
			SELECT
 							CountryId,
								CountryName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''CountryId'' THEN CountryId
						 
						WHEN @OrderColume=''CountryName'' THEN CountryName
									ELSE 
							    CountryId
					        	END
			) AS "RowNumber"
  			FROM Countries 
			where 
								(CountryId = @CountryId OR @CountryId Is Null)
										AND(CountryName LIKE @CountryName OR @CountryName Is Null)
								)
			SELECT  *
			FROM CTE_Countries
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Countries_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Countries_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Countries_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Countries_Delete
		@CountryId Int 		  =  NULL 
					AS
			Delete From Countries
			Where 	
								(CountryId=@CountryId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Countries_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Countries_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Countries_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Countries_Insert
	@CountryName NVarChar (256)  =  NULL 
		AS
			INSERT INTO Countries
			(
									CountryName
									)
			Values(
									@CountryName
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Countries_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Countries_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Countries_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Countries_UPDATE
												@CountryId Int 							  =  NULL 
														, @CountryName NVarChar 							(256)  =  NULL 
												AS
					UPDATE Countries SET
													CountryName=@CountryName 
													Where 	
												CountryId=@CountryId
							'					
					PRINT 'dbo.SP_Countries_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Nationalities_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Nationalities_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Nationalities_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @NationalityId Int   =  NULL 
, @NationalityName NVarChar (256)  =  NULL 
	AS
			WITH CTE_Nationalities AS
			( 
			SELECT
 							NationalityId,
								NationalityName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''NationalityId'' THEN NationalityId
						 
						WHEN @OrderColume=''NationalityName'' THEN NationalityName
									ELSE 
							    NationalityId
					        	END
			) AS "RowNumber"
  			FROM Nationalities 
			where 
								(NationalityId = @NationalityId OR @NationalityId Is Null)
										AND(NationalityName LIKE @NationalityName OR @NationalityName Is Null)
								)
			SELECT  *
			FROM CTE_Nationalities
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Nationalities_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Nationalities_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Nationalities_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Nationalities_Delete
		@NationalityId Int 		  =  NULL 
					AS
			Delete From Nationalities
			Where 	
								(NationalityId=@NationalityId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Nationalities_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Nationalities_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Nationalities_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Nationalities_Insert
	@NationalityName NVarChar (256)  =  NULL 
		AS
			INSERT INTO Nationalities
			(
									NationalityName
									)
			Values(
									@NationalityName
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Nationalities_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Nationalities_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Nationalities_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Nationalities_UPDATE
												@NationalityId Int 							  =  NULL 
														, @NationalityName NVarChar 							(256)  =  NULL 
												AS
					UPDATE Nationalities SET
													NationalityName=@NationalityName 
													Where 	
												NationalityId=@NationalityId
							'					
					PRINT 'dbo.SP_Nationalities_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Modules_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Modules_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Modules_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @ModuleId Int   =  NULL 
, @ModuleName NVarChar (256)  =  NULL 
	AS
			WITH CTE_Modules AS
			( 
			SELECT
 							ModuleId,
								ModuleName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''ModuleId'' THEN ModuleId
						 
						WHEN @OrderColume=''ModuleName'' THEN ModuleName
									ELSE 
							    ModuleId
					        	END
			) AS "RowNumber"
  			FROM Modules 
			where 
								(ModuleId = @ModuleId OR @ModuleId Is Null)
										AND(ModuleName LIKE @ModuleName OR @ModuleName Is Null)
								)
			SELECT  *
			FROM CTE_Modules
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Modules_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Modules_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Modules_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Modules_Delete
		@ModuleId Int 		  =  NULL 
					AS
			Delete From Modules
			Where 	
								(ModuleId=@ModuleId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Modules_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Modules_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Modules_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Modules_Insert
	@ModuleName NVarChar (256)  =  NULL 
		AS
			INSERT INTO Modules
			(
									ModuleName
									)
			Values(
									@ModuleName
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Modules_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Modules_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Modules_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Modules_UPDATE
												@ModuleId Int 							  =  NULL 
														, @ModuleName NVarChar 							(256)  =  NULL 
												AS
					UPDATE Modules SET
													ModuleName=@ModuleName 
													Where 	
												ModuleId=@ModuleId
							'					
					PRINT 'dbo.SP_Modules_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_News_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_News_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_News_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @CreationDate DateTime   =  NULL 
, @Description NVarChar (Max)  =  NULL 
, @Link NVarChar (256)  =  NULL 
, @NewsId Int   =  NULL 
, @Title NVarChar (50)  =  NULL 
	AS
			WITH CTE_News AS
			( 
			SELECT
 							CreationDate,
								Description,
								Link,
								NewsId,
								Title,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''CreationDate'' THEN CreationDate
						 
						WHEN @OrderColume=''Description'' THEN Description
						 
						WHEN @OrderColume=''Link'' THEN Link
												WHEN @OrderColume=''NewsId'' THEN NewsId
						 
						WHEN @OrderColume=''Title'' THEN Title
									ELSE 
							    NewsId
					        	END
			) AS "RowNumber"
  			FROM News 
			where 
								(CreationDate = @CreationDate OR @CreationDate Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(Link LIKE @Link OR @Link Is Null)
										AND(NewsId = @NewsId OR @NewsId Is Null)
										AND(Title LIKE @Title OR @Title Is Null)
								)
			SELECT  *
			FROM CTE_News
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_News_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_News_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_News_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_News_Delete
		@NewsId Int 		  =  NULL 
					AS
			Delete From News
			Where 	
								(NewsId=@NewsId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_News_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_News_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_News_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_News_Insert
	@CreationDate DateTime   =  NULL 
		, @Description NVarChar (Max)  =  NULL 
		, @Link NVarChar (256)  =  NULL 
		, @Title NVarChar (50)  =  NULL 
		AS
			INSERT INTO News
			(
									CreationDate
												,Description
												,Link
												,Title
									)
			Values(
									@CreationDate
												,@Description
												,@Link
												,@Title
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_News_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_News_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_News_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_News_UPDATE
												@CreationDate DateTime 							  =  NULL 
														, @Description NVarChar (Max)							  =  NULL 
														, @Link NVarChar 							(256)  =  NULL 
														, @NewsId Int 							  =  NULL 
														, @Title NVarChar 							(50)  =  NULL 
												AS
					UPDATE News SET
													CreationDate=@CreationDate 
																,Description=@Description
																,Link=@Link
																,Title=@Title
													Where 	
												NewsId=@NewsId
							'					
					PRINT 'dbo.SP_News_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberCommitties_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberCommitties_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberCommitties_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Church NVarChar (256)  =  NULL 
, @CommittieID Int   =  NULL 
, @DayOfMonth TinyInt   =  NULL 
, @DayOfWeek TinyInt   =  NULL 
, @IsServant Bit   =  NULL 
, @MemberId Int   =  NULL 
, @Name NVarChar (256)  =  NULL 
, @OccurreEveryMonths TinyInt   =  NULL 
, @OccurreEveryWeeks Int   =  NULL 
, @OccurrenceType TinyInt   =  NULL 
, @ServantType NVarChar (256)  =  NULL 
, @TimeOfWeek DateTime   =  NULL 
, @WeekOfMonth TinyInt   =  NULL 
	AS
			WITH CTE_MemberCommitties AS
			( 
			SELECT
 							Church,
								CommittieID,
								DayOfMonth,
								DayOfWeek,
								IsServant,
								MemberId,
								Name,
								OccurreEveryMonths,
								OccurreEveryWeeks,
								OccurrenceType,
								ServantType,
								TimeOfWeek,
								WeekOfMonth,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''Church'' THEN Church
												WHEN @OrderColume=''CommittieID'' THEN CommittieID
												WHEN @OrderColume=''DayOfMonth'' THEN DayOfMonth
												WHEN @OrderColume=''DayOfWeek'' THEN DayOfWeek
												WHEN @OrderColume=''IsServant'' THEN IsServant
												WHEN @OrderColume=''MemberId'' THEN MemberId
						 
						WHEN @OrderColume=''Name'' THEN Name
												WHEN @OrderColume=''OccurreEveryMonths'' THEN OccurreEveryMonths
												WHEN @OrderColume=''OccurreEveryWeeks'' THEN OccurreEveryWeeks
												WHEN @OrderColume=''OccurrenceType'' THEN OccurrenceType
						 
						WHEN @OrderColume=''ServantType'' THEN ServantType
												WHEN @OrderColume=''TimeOfWeek'' THEN TimeOfWeek
												WHEN @OrderColume=''WeekOfMonth'' THEN WeekOfMonth
									ELSE 
							    CommittieID
					        	END
			) AS "RowNumber"
  			FROM MemberCommitties 
			where 
								(Church LIKE @Church OR @Church Is Null)
										AND(CommittieID = @CommittieID OR @CommittieID Is Null)
										AND(DayOfMonth = @DayOfMonth OR @DayOfMonth Is Null)
										AND(DayOfWeek = @DayOfWeek OR @DayOfWeek Is Null)
										AND(IsServant = @IsServant OR @IsServant Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Name LIKE @Name OR @Name Is Null)
										AND(OccurreEveryMonths = @OccurreEveryMonths OR @OccurreEveryMonths Is Null)
										AND(OccurreEveryWeeks = @OccurreEveryWeeks OR @OccurreEveryWeeks Is Null)
										AND(OccurrenceType = @OccurrenceType OR @OccurrenceType Is Null)
										AND(ServantType LIKE @ServantType OR @ServantType Is Null)
										AND(TimeOfWeek = @TimeOfWeek OR @TimeOfWeek Is Null)
										AND(WeekOfMonth = @WeekOfMonth OR @WeekOfMonth Is Null)
								)
			SELECT  *
			FROM CTE_MemberCommitties
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberCommitties_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberCommitties_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberCommitties_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberCommitties_Delete
		@CommittieID Int 		  =  NULL 
					AS
			Delete From MemberCommitties
			Where 	
								(CommittieID=@CommittieID)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MemberCommitties_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberCommitties_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberCommitties_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberCommitties_Insert
	@Church NVarChar (256)  =  NULL 
		, @DayOfMonth TinyInt   =  NULL 
		, @DayOfWeek TinyInt   =  NULL 
		, @IsServant Bit   =  NULL 
		, @MemberId Int   =  NULL 
		, @Name NVarChar (256)  =  NULL 
		, @OccurreEveryMonths TinyInt   =  NULL 
		, @OccurreEveryWeeks Int   =  NULL 
		, @OccurrenceType TinyInt   =  NULL 
		, @ServantType NVarChar (256)  =  NULL 
		, @TimeOfWeek DateTime   =  NULL 
		, @WeekOfMonth TinyInt   =  NULL 
		AS
			INSERT INTO MemberCommitties
			(
									Church
												,DayOfMonth
												,DayOfWeek
												,IsServant
												,MemberId
												,Name
												,OccurreEveryMonths
												,OccurreEveryWeeks
												,OccurrenceType
												,ServantType
												,TimeOfWeek
												,WeekOfMonth
									)
			Values(
									@Church
												,@DayOfMonth
												,@DayOfWeek
												,@IsServant
												,@MemberId
												,@Name
												,@OccurreEveryMonths
												,@OccurreEveryWeeks
												,@OccurrenceType
												,@ServantType
												,@TimeOfWeek
												,@WeekOfMonth
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MemberCommitties_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberCommitties_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberCommitties_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_MemberCommitties_UPDATE
												@Church NVarChar 							(256)  =  NULL 
														, @CommittieID Int 							  =  NULL 
														, @DayOfMonth TinyInt 							  =  NULL 
														, @DayOfWeek TinyInt 							  =  NULL 
														, @IsServant Bit 							  =  NULL 
														, @MemberId Int 							  =  NULL 
														, @Name NVarChar 							(256)  =  NULL 
														, @OccurreEveryMonths TinyInt 							  =  NULL 
														, @OccurreEveryWeeks Int 							  =  NULL 
														, @OccurrenceType TinyInt 							  =  NULL 
														, @ServantType NVarChar 							(256)  =  NULL 
														, @TimeOfWeek DateTime 							  =  NULL 
														, @WeekOfMonth TinyInt 							  =  NULL 
												AS
					UPDATE MemberCommitties SET
													Church=@Church 
																,DayOfMonth=@DayOfMonth
																,DayOfWeek=@DayOfWeek
																,IsServant=@IsServant
																,MemberId=@MemberId
																,Name=@Name
																,OccurreEveryMonths=@OccurreEveryMonths
																,OccurreEveryWeeks=@OccurreEveryWeeks
																,OccurrenceType=@OccurrenceType
																,ServantType=@ServantType
																,TimeOfWeek=@TimeOfWeek
																,WeekOfMonth=@WeekOfMonth
													Where 	
												CommittieID=@CommittieID
							'					
					PRINT 'dbo.SP_MemberCommitties_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberEducation_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberEducation_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberEducation_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @EducationId Int   =  NULL 
, @MemberId Int   =  NULL 
, @Name NVarChar (256)  =  NULL 
, @Type TinyInt   =  NULL 
, @Year Int   =  NULL 
	AS
			WITH CTE_MemberEducation AS
			( 
			SELECT
 							EducationId,
								MemberId,
								Name,
								Type,
								Year,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''EducationId'' THEN EducationId
												WHEN @OrderColume=''MemberId'' THEN MemberId
						 
						WHEN @OrderColume=''Name'' THEN Name
												WHEN @OrderColume=''Type'' THEN Type
												WHEN @OrderColume=''Year'' THEN Year
									ELSE 
							    EducationId
					        	END
			) AS "RowNumber"
  			FROM MemberEducation 
			where 
								(EducationId = @EducationId OR @EducationId Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Name LIKE @Name OR @Name Is Null)
										AND(Type = @Type OR @Type Is Null)
										AND(Year = @Year OR @Year Is Null)
								)
			SELECT  *
			FROM CTE_MemberEducation
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberEducation_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberEducation_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberEducation_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberEducation_Delete
		@EducationId Int 		  =  NULL 
					AS
			Delete From MemberEducation
			Where 	
								(EducationId=@EducationId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MemberEducation_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberEducation_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberEducation_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberEducation_Insert
	@MemberId Int   =  NULL 
		, @Name NVarChar (256)  =  NULL 
		, @Type TinyInt   =  NULL 
		, @Year Int   =  NULL 
		AS
			INSERT INTO MemberEducation
			(
									MemberId
												,Name
												,Type
												,Year
									)
			Values(
									@MemberId
												,@Name
												,@Type
												,@Year
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MemberEducation_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberEducation_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberEducation_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_MemberEducation_UPDATE
												@EducationId Int 							  =  NULL 
														, @MemberId Int 							  =  NULL 
														, @Name NVarChar 							(256)  =  NULL 
														, @Type TinyInt 							  =  NULL 
														, @Year Int 							  =  NULL 
												AS
					UPDATE MemberEducation SET
													MemberId=@MemberId 
																,Name=@Name
																,Type=@Type
																,Year=@Year
													Where 	
												EducationId=@EducationId
							'					
					PRINT 'dbo.SP_MemberEducation_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberJobs_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberJobs_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberJobs_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @EmployedFrom DateTime   =  NULL 
, @EmployedTo DateTime   =  NULL 
, @Employeer NVarChar (50)  =  NULL 
, @IsCurrentJob Bit   =  NULL 
, @JobId Int   =  NULL 
, @MemberId Int   =  NULL 
, @Position NVarChar (50)  =  NULL 
	AS
			WITH CTE_MemberJobs AS
			( 
			SELECT
 							EmployedFrom,
								EmployedTo,
								Employeer,
								IsCurrentJob,
								JobId,
								MemberId,
								Position,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''EmployedFrom'' THEN EmployedFrom
												WHEN @OrderColume=''EmployedTo'' THEN EmployedTo
						 
						WHEN @OrderColume=''Employeer'' THEN Employeer
												WHEN @OrderColume=''IsCurrentJob'' THEN IsCurrentJob
												WHEN @OrderColume=''JobId'' THEN JobId
												WHEN @OrderColume=''MemberId'' THEN MemberId
						 
						WHEN @OrderColume=''Position'' THEN Position
									ELSE 
							    JobId
					        	END
			) AS "RowNumber"
  			FROM MemberJobs 
			where 
								(EmployedFrom = @EmployedFrom OR @EmployedFrom Is Null)
										AND(EmployedTo = @EmployedTo OR @EmployedTo Is Null)
										AND(Employeer LIKE @Employeer OR @Employeer Is Null)
										AND(IsCurrentJob = @IsCurrentJob OR @IsCurrentJob Is Null)
										AND(JobId = @JobId OR @JobId Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Position LIKE @Position OR @Position Is Null)
								)
			SELECT  *
			FROM CTE_MemberJobs
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberJobs_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberJobs_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberJobs_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberJobs_Delete
		@JobId Int 		  =  NULL 
					AS
			Delete From MemberJobs
			Where 	
								(JobId=@JobId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_MemberJobs_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberJobs_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberJobs_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberJobs_Insert
	@EmployedFrom DateTime   =  NULL 
		, @EmployedTo DateTime   =  NULL 
		, @Employeer NVarChar (50)  =  NULL 
		, @IsCurrentJob Bit   =  NULL 
		, @MemberId Int   =  NULL 
		, @Position NVarChar (50)  =  NULL 
		AS
			INSERT INTO MemberJobs
			(
									EmployedFrom
												,EmployedTo
												,Employeer
												,IsCurrentJob
												,MemberId
												,Position
									)
			Values(
									@EmployedFrom
												,@EmployedTo
												,@Employeer
												,@IsCurrentJob
												,@MemberId
												,@Position
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_MemberJobs_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberJobs_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberJobs_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_MemberJobs_UPDATE
												@EmployedFrom DateTime 							  =  NULL 
														, @EmployedTo DateTime 							  =  NULL 
														, @Employeer NVarChar 							(50)  =  NULL 
														, @IsCurrentJob Bit 							  =  NULL 
														, @JobId Int 							  =  NULL 
														, @MemberId Int 							  =  NULL 
														, @Position NVarChar 							(50)  =  NULL 
												AS
					UPDATE MemberJobs SET
													EmployedFrom=@EmployedFrom 
																,EmployedTo=@EmployedTo
																,Employeer=@Employeer
																,IsCurrentJob=@IsCurrentJob
																,MemberId=@MemberId
																,Position=@Position
													Where 	
												JobId=@JobId
							'					
					PRINT 'dbo.SP_MemberJobs_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Emails_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Emails_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Emails_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Code Char (5)  =  NULL 
, @Description NVarChar (Max)  =  NULL 
, @EmailId Int   =  NULL 
, @Subject NVarChar (256)  =  NULL 
, @Template NVarChar (Max)  =  NULL 
, @TemplateResourcesPaths NVarChar (256)  =  NULL 
	AS
			WITH CTE_Emails AS
			( 
			SELECT
 							Code,
								Description,
								EmailId,
								Subject,
								Template,
								TemplateResourcesPaths,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''Code'' THEN Code
						 
						WHEN @OrderColume=''Description'' THEN Description
												WHEN @OrderColume=''EmailId'' THEN EmailId
						 
						WHEN @OrderColume=''Subject'' THEN Subject
						 
						WHEN @OrderColume=''Template'' THEN Template
						 
						WHEN @OrderColume=''TemplateResourcesPaths'' THEN TemplateResourcesPaths
									ELSE 
							    EmailId
					        	END
			) AS "RowNumber"
  			FROM Emails 
			where 
								(Code LIKE @Code OR @Code Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(EmailId = @EmailId OR @EmailId Is Null)
										AND(Subject LIKE @Subject OR @Subject Is Null)
										AND(Template LIKE @Template OR @Template Is Null)
										AND(TemplateResourcesPaths LIKE @TemplateResourcesPaths OR @TemplateResourcesPaths Is Null)
								)
			SELECT  *
			FROM CTE_Emails
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Emails_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Emails_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Emails_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Emails_Delete
		@EmailId Int 		  =  NULL 
					AS
			Delete From Emails
			Where 	
								(EmailId=@EmailId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Emails_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Emails_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Emails_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Emails_Insert
	@Code Char (5)  =  NULL 
		, @Description NVarChar (Max)  =  NULL 
		, @Subject NVarChar (256)  =  NULL 
		, @Template NVarChar (Max)  =  NULL 
		, @TemplateResourcesPaths NVarChar (256)  =  NULL 
		AS
			INSERT INTO Emails
			(
									Code
												,Description
												,Subject
												,Template
												,TemplateResourcesPaths
									)
			Values(
									@Code
												,@Description
												,@Subject
												,@Template
												,@TemplateResourcesPaths
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Emails_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Emails_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Emails_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Emails_UPDATE
												@Code Char 							(5)  =  NULL 
														, @Description NVarChar (Max)							  =  NULL 
														, @EmailId Int 							  =  NULL 
														, @Subject NVarChar 							(256)  =  NULL 
														, @Template NVarChar (Max)							  =  NULL 
														, @TemplateResourcesPaths NVarChar 							(256)  =  NULL 
												AS
					UPDATE Emails SET
													Code=@Code 
																,Description=@Description
																,Subject=@Subject
																,Template=@Template
																,TemplateResourcesPaths=@TemplateResourcesPaths
													Where 	
												EmailId=@EmailId
							'					
					PRINT 'dbo.SP_Emails_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Errors_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Errors_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Errors_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Code Char (5)  =  NULL 
, @Description NVarChar (256)  =  NULL 
, @ErrorId Int   =  NULL 
, @Message NVarChar (256)  =  NULL 
	AS
			WITH CTE_Errors AS
			( 
			SELECT
 							Code,
								Description,
								ErrorId,
								Message,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''Code'' THEN Code
						 
						WHEN @OrderColume=''Description'' THEN Description
												WHEN @OrderColume=''ErrorId'' THEN ErrorId
						 
						WHEN @OrderColume=''Message'' THEN Message
									ELSE 
							    ErrorId
					        	END
			) AS "RowNumber"
  			FROM Errors 
			where 
								(Code LIKE @Code OR @Code Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(ErrorId = @ErrorId OR @ErrorId Is Null)
										AND(Message LIKE @Message OR @Message Is Null)
								)
			SELECT  *
			FROM CTE_Errors
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Errors_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Errors_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Errors_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Errors_Delete
		@ErrorId Int 		  =  NULL 
					AS
			Delete From Errors
			Where 	
								(ErrorId=@ErrorId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Errors_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Errors_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Errors_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Errors_Insert
	@Code Char (5)  =  NULL 
		, @Description NVarChar (256)  =  NULL 
		, @Message NVarChar (256)  =  NULL 
		AS
			INSERT INTO Errors
			(
									Code
												,Description
												,Message
									)
			Values(
									@Code
												,@Description
												,@Message
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Errors_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Errors_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Errors_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Errors_UPDATE
												@Code Char 							(5)  =  NULL 
														, @Description NVarChar 							(256)  =  NULL 
														, @ErrorId Int 							  =  NULL 
														, @Message NVarChar 							(256)  =  NULL 
												AS
					UPDATE Errors SET
													Code=@Code 
																,Description=@Description
																,Message=@Message
													Where 	
												ErrorId=@ErrorId
							'					
					PRINT 'dbo.SP_Errors_Update done'
					/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Notifications_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Notifications_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Notifications_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Code Char (5)  =  NULL 
, @Description NVarChar (Max)  =  NULL 
, @NotificationId Int   =  NULL 
, @Template NVarChar (Max)  =  NULL 
	AS
			WITH CTE_Notifications AS
			( 
			SELECT
 							Code,
								Description,
								NotificationId,
								Template,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''Code'' THEN Code
						 
						WHEN @OrderColume=''Description'' THEN Description
												WHEN @OrderColume=''NotificationId'' THEN NotificationId
						 
						WHEN @OrderColume=''Template'' THEN Template
									ELSE 
							    NotificationId
					        	END
			) AS "RowNumber"
  			FROM Notifications 
			where 
								(Code LIKE @Code OR @Code Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(NotificationId = @NotificationId OR @NotificationId Is Null)
										AND(Template LIKE @Template OR @Template Is Null)
								)
			SELECT  *
			FROM CTE_Notifications
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_Notifications_SELECT done'
 /*===========================Start Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Notifications_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Notifications_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Notifications_Delete
		@NotificationId Int 		  =  NULL 
					AS
			Delete From Notifications
			Where 	
								(NotificationId=@NotificationId)
					'
/*===========================End Delete PROCEDURE===========================*/
PRINT 'dbo.SP_Notifications_Delete done'
/*===========================Start Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Notifications_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Notifications_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Notifications_Insert
	@Code Char (5)  =  NULL 
		, @Description NVarChar (Max)  =  NULL 
		, @Template NVarChar (Max)  =  NULL 
		AS
			INSERT INTO Notifications
			(
									Code
												,Description
												,Template
									)
			Values(
									@Code
												,@Description
												,@Template
						)
			return @@identity'
/*===========================End Insert PROCEDURE===========================*/
PRINT 'dbo.SP_Notifications_Insert done'
/*===========================Start Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Notifications_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Notifications_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Notifications_UPDATE
												@Code Char 							(5)  =  NULL 
														, @Description NVarChar (Max)							  =  NULL 
														, @NotificationId Int 							  =  NULL 
														, @Template NVarChar (Max)							  =  NULL 
												AS
					UPDATE Notifications SET
													Code=@Code 
																,Description=@Description
																,Template=@Template
													Where 	
												NotificationId=@NotificationId
							'					
					PRINT 'dbo.SP_Notifications_Update done'
					

/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_MemberModulesDTO_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_MemberModulesDTO_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_MemberModulesDTO_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @MemberId Int   =  NULL 


, @ModuleId Int   =  NULL 


, @DisplayName NVarChar (256)  =  NULL 


, @Email NVarChar (256)  =  NULL 


, @ModuleName NVarChar (256)  =  NULL 

	AS
			WITH CTE_MemberModules AS
			( 
			SELECT
 							v.MemberId,
								v.ModuleId,
								v.DisplayName,
								v.Email,
								v.ModuleName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''MemberId'' THEN v.MemberId
												WHEN @OrderColume=''ModuleId'' THEN v.ModuleId
						 
						WHEN @OrderColume=''DisplayName'' THEN v.DisplayName
						 
						WHEN @OrderColume=''Email'' THEN v.Email
						 
						WHEN @OrderColume=''ModuleName'' THEN v.ModuleName
						        	END
			) AS "RowNumber"
  			FROM MemberModules v 
			where 
								(MemberId=@MemberId OR @MemberId Is Null)
										AND(ModuleId=@ModuleId OR @ModuleId Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(ModuleName=@ModuleName OR @ModuleName Is Null)
								)
			SELECT *
			FROM CTE_MemberModules
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM MemberModules v
            where 
								(v.MemberId=@MemberId OR @MemberId Is Null)
										AND(v.ModuleId=@ModuleId OR @ModuleId Is Null)
										AND(v.DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(v.Email=@Email OR @Email Is Null)
										AND(v.ModuleName=@ModuleName OR @ModuleName Is Null)
					 	
			)'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_MemberModulesDTO_SELECT done'
/*===========================Start Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_SecureMemberDTO_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_SecureMemberDTO_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_SecureMemberDTO_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @MemberId Int   =  NULL 


, @FirstName NVarChar (256)  =  NULL 


, @LastName NVarChar (256)  =  NULL 


, @DisplayName NVarChar (256)  =  NULL 


, @Email NVarChar (256)  =  NULL 


, @Gender TinyInt   =  NULL 


, @Birthdate DateTime   =  NULL 


, @NationalityId Int   =  NULL 


, @CountryId Int   =  NULL 


, @CreationDate DateTime   =  NULL 


, @IsActive Bit   =  NULL 


, @LastUpdateDate DateTime   =  NULL 


, @ImagePath NVarChar (50)  =  NULL 


, @ConfessionFather NChar (10)  =  NULL 

	AS
			WITH CTE_SecureMember AS
			( 
			SELECT
 							v.MemberId,
								v.FirstName,
								v.LastName,
								v.DisplayName,
								v.Email,
								v.Gender,
								v.Birthdate,
								v.NationalityId,
								v.CountryId,
								v.CreationDate,
								v.IsActive,
								v.LastUpdateDate,
								v.ImagePath,
								v.ConfessionFather,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''MemberId'' THEN v.MemberId
						 
						WHEN @OrderColume=''FirstName'' THEN v.FirstName
						 
						WHEN @OrderColume=''LastName'' THEN v.LastName
						 
						WHEN @OrderColume=''DisplayName'' THEN v.DisplayName
						 
						WHEN @OrderColume=''Email'' THEN v.Email
												WHEN @OrderColume=''Gender'' THEN v.Gender
												WHEN @OrderColume=''Birthdate'' THEN v.Birthdate
												WHEN @OrderColume=''NationalityId'' THEN v.NationalityId
												WHEN @OrderColume=''CountryId'' THEN v.CountryId
												WHEN @OrderColume=''CreationDate'' THEN v.CreationDate
												WHEN @OrderColume=''IsActive'' THEN v.IsActive
												WHEN @OrderColume=''LastUpdateDate'' THEN v.LastUpdateDate
						 
						WHEN @OrderColume=''ImagePath'' THEN v.ImagePath
						 
						WHEN @OrderColume=''ConfessionFather'' THEN v.ConfessionFather
						        	END
			) AS "RowNumber"
  			FROM SecureMember v 
			where 
								(MemberId=@MemberId OR @MemberId Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(LastName=@LastName OR @LastName Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(Gender=@Gender OR @Gender Is Null)
										AND(Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(CountryId=@CountryId OR @CountryId Is Null)
										AND(CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(IsActive=@IsActive OR @IsActive Is Null)
										AND(LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
								)
			SELECT *
			FROM CTE_SecureMember
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM SecureMember v
            where 
								(v.MemberId=@MemberId OR @MemberId Is Null)
										AND(v.FirstName=@FirstName OR @FirstName Is Null)
										AND(v.LastName=@LastName OR @LastName Is Null)
										AND(v.DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(v.Email=@Email OR @Email Is Null)
										AND(v.Gender=@Gender OR @Gender Is Null)
										AND(v.Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(v.NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(v.CountryId=@CountryId OR @CountryId Is Null)
										AND(v.CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(v.IsActive=@IsActive OR @IsActive Is Null)
										AND(v.LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(v.ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(v.ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
					 	
			)'
 /*===========================End Select PROCEDURE===========================*/
PRINT 'dbo.SP_SecureMemberDTO_SELECT done'
